package com.situ.web.servlet;

import com.situ.web.entity.Banji;
import com.situ.web.entity.Student;
import com.situ.web.entity.User;
import com.situ.web.util.JDBCUtil;
import com.situ.web.vo.StudentBanjiVO;
//import jdk.dynalink.linker.LinkerServices;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


//查询学生信息
@WebServlet("/student")
public class StudentServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
//        req.setCharacterEncoding("UTF-8");

//        HttpSession session = req.getSession();
//        User user = (User)session.getAttribute("user");
//        if(user == null){
//            resp.sendRedirect(req.getContextPath()+"/login.jsp");
//            return;
//        }

        System.out.println("HelloServlet.service");
        String method = req.getParameter("method");
        if(method == null){
            method = "selectAll";
        }
        switch (method){
            case "selectAll":
                selectAll(req,resp);
                break;
            case "deleteById":
                deleteById(req,resp);
                break;
            case "insert":
                insert(req,resp);
                break;
            case "selectById":
                selectById(req,resp);
                break;
            case "update":
                update(req,resp);
                break;
            case "getStudentInsertPage":
                getStudentInsertPage(req,resp);
                break;
            default:break;
        }
    }

    private void getStudentInsertPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
         Connection connection = null;
         PreparedStatement statement = null;
         ResultSet resultSet = null;
         List<Banji> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnnection();
            String sql = "select id,`name` from banji";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                Banji banji = new Banji(id,name);
                list.add(banji);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCUtil.close(connection,statement,resultSet);
        }
        req.setAttribute("banjiList",list);
        req.getRequestDispatcher("/student_insert.jsp").forward(req,resp);
    }

    private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        String name = req.getParameter("name");
        int age = Integer.parseInt(req.getParameter("age"));
        String gender = req.getParameter("gender");
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtil.getConnnection();
            String sql = "update student set `name`=?,age=?,gender=? where id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(4,id);
            statement.setString(1,name);
            statement.setInt(2,age);
            statement.setString(3,gender);
            System.out.println(statement);
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCUtil.close(connection,statement,null);
        }
        //转发
        resp.sendRedirect(req.getContextPath()+"/student?method=selectAll");

    }

    private void selectById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Student student = null;

        try {
            connection = JDBCUtil.getConnnection();
            String sql = "select `name`,age,gender from student where id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                student = new Student(id,resultSet.getString("name"),
                        resultSet.getInt("age"),
                        resultSet.getString("gender"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCUtil.close(connection,statement,resultSet);
        }

        req.setAttribute("student",student);
        req.getRequestDispatcher("/student_update.jsp").forward(req,resp);
    }

    private void insert(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        String name = req.getParameter("name");
        String age = req.getParameter("age");
        String gender = req.getParameter("gender");
        String banjiId = req.getParameter("banjiId");
        System.out.println(name+" "+age+" "+gender);
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtil.getConnnection();
            String sql = "insert into student(`name`,age,gender,banji_id) values(?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,name);
            statement.setInt(2,Integer.parseInt(age));
            statement.setString(3,gender);
            statement.setInt(4,Integer.parseInt(banjiId));
            int count = statement.executeUpdate();
            System.out.println(statement);
            System.out.println("count:"+count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCUtil.close(connection,statement,null);
        }
        //删除完成后查询新的数据
        //重定向 /JavaWeb/selectAll
        resp.sendRedirect(req.getContextPath()+"/student?method=selectAll");
    }

    //删除信息
    private void deleteById(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        String id = req.getParameter("id");
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtil.getConnnection();
            String sql = "delete from student where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,Integer.parseInt(id));
            int count = statement.executeUpdate();
            System.out.println("count:"+count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCUtil.close(connection,statement,null);
        }
        //删除完成后查询新的数据
        //重定向 /JavaWeb/selectAll
        resp.sendRedirect(req.getContextPath()+"/student?method=selectAll");
    }
//    查询所有学生信息
//    private void selectAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//        Connection connection = null;
//        PreparedStatement statement = null;
//        ResultSet resultSet = null;
//        ArrayList<Student> list = new ArrayList<>();
//        try {
//            connection = JDBCUtil.getConnnection();
//            String sql = "SELECT id,`name`,age,gender FROM student";
//            statement = connection.prepareStatement(sql);
//            resultSet = statement.executeQuery();
//            while (resultSet.next()){
//                int id = resultSet.getInt("id");
//                String name = resultSet.getString("name");
//                int age = resultSet.getInt("age");
//                String gender = resultSet.getString("gender");
//                Student student = new Student(id,name,age,gender);
//                list.add(student);
//            }
//            for (Student student : list) {
//                System.out.println(student);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        finally {
//            JDBCUtil.close(connection,statement,resultSet);
//        }
//        //把list数据放在一块内存区域中
//        req.setAttribute("list",list);
//        //跳转到student_list.jsp中展示数据
//        req.getRequestDispatcher("/student_list.jsp").forward(req,resp);
//    }
    private void selectAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("StudentServlet.selectAll");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        ArrayList<StudentBanjiVO> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnnection();
            String sql = "SELECT s.id AS studentId,s.name AS studentName,s.age AS studentAge,s.gender AS studentGender,b.name AS banjiName\n" +
                    "FROM student AS s INNER JOIN banji AS b \n" +
                    "ON s.banji_id=b.id;";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                int studentId = resultSet.getInt("studentId");
                String studentName = resultSet.getString("studentName");
                int studentAge = resultSet.getInt("studentAge");
                String studentGender = resultSet.getString("studentGender");
                String banjiName = resultSet.getString("banjiName");
                StudentBanjiVO studentBanji = new StudentBanjiVO(studentId, studentName, studentAge,studentGender, banjiName);

                list.add(studentBanji);
            }
            for (StudentBanjiVO studentBanji : list) {
                System.out.println(studentBanji);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(connection, statement, resultSet);
        }

        // 把list数据放到一块内存区域里面
        req.setAttribute("list", list);
        // 跳转到student_list.jsp展示数据
        // Dispatcher:分发  forward：转发
        req.getRequestDispatcher("/student_list.jsp").forward(req, resp);
    }
}
